﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public sealed class HisNameDAL : IHisNameDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public HisNameDAL()
        {

        }
        public IList<HisNameInfo> ListHisNames(HisNameInfo hisInfo)
        {
            string strSQL;
            int empSeq = hisInfo.Employee == null ? 0 : hisInfo.Employee.Sequence;

            List<HisNameInfo> infoList = new List<HisNameInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", hisInfo.Sequence);
                dbManager.AddParameters(2, "@empSequence", empSeq);

                strSQL = "SELECT n.NAME_HIS_SEQ, n.CHANGED_DATE, n.CHANGED_REF_NO, n.OLD_NAME, n.OLD_LAST_NAME"
                        + ", n.NEW_NAME, n.NEW_LAST_NAME, n.EMP_SEQ, n.OLD_PREFIX_SEQ, n.NEW_PREFIX_SEQ"
                        + ", n.REMARKS, n.RECORD_STATUS, n.CREATED_BY, n.UPDATED_BY"
                    + " FROM HRM_PS_EMP_NAME_HISTORIES n"
                    + " WHERE n.RECORD_STATUS not like @DeleteRecord"
                    + (hisInfo.Sequence > 0 ? " AND n.NAME_HIS_SEQ = @Sequence" : "")
                    + (empSeq > 0 ? " AND n.EMP_SEQ = @empSequence" : "");

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    EmployeeDAL empDAL = new EmployeeDAL();
                    EmployeeInfo empInfo = new EmployeeInfo();
                    if (!rdr.IsDBNull(7))
                    {
                        IList<EmployeeInfo> empList = empDAL.ListEmployees(new EmployeeInfo(rdr.GetInt32(7)));
                        empInfo = empList[0];
                    }

                    MasNamePrefixDAL pfDAL = new MasNamePrefixDAL();
                    MasNamePrefixInfo opfInfo = new MasNamePrefixInfo();
                    if (!rdr.IsDBNull(8))
                    {
                        IList<MasNamePrefixInfo> pfList = pfDAL.ListNamePrefixes(new MasNamePrefixInfo(rdr.GetInt32(8)));
                        opfInfo = pfList[0];
                    }
                    MasNamePrefixInfo npfInfo = new MasNamePrefixInfo();
                    if (!rdr.IsDBNull(9))
                    {
                        IList<MasNamePrefixInfo> pfList = pfDAL.ListNamePrefixes(new MasNamePrefixInfo(rdr.GetInt32(9)));
                        npfInfo = pfList[0];
                    }

                    HisNameInfo Info = new HisNameInfo(rdr.GetInt32(0)
                            , rdr.IsDBNull(1) ? (DateTime?)null : rdr.GetDateTime(1)
                            , rdr.IsDBNull(2) ? "" : rdr.GetString(2)
                            , rdr.IsDBNull(3) ? "" : rdr.GetString(3)
                            , rdr.IsDBNull(4) ? "" : rdr.GetString(4)
                            , rdr.IsDBNull(5) ? "" : rdr.GetString(5)
                            , rdr.IsDBNull(6) ? "" : rdr.GetString(6)
                            , empInfo, opfInfo, npfInfo
                            , rdr.IsDBNull(10) ? "" : rdr.GetString(10)
                            , rdr.IsDBNull(11) ? "" : rdr.GetString(11)
                            , rdr.IsDBNull(12) ? (int?)null : rdr.GetInt32(12)
                            , rdr.IsDBNull(13) ? (int?)null : rdr.GetInt32(13));
                    infoList.Add(Info);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return infoList;
        }
        public DataSet LoadHisNames(HisNameInfo hisInfo)
        {
            string strSQL;
            int empSeq = hisInfo.Employee == null ? 0 : hisInfo.Employee.Sequence;

            DataSet ds = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@Sequence", hisInfo.Sequence);
                dbManager.AddParameters(2, "@empSequence", empSeq);

                strSQL = "SELECT n.NAME_HIS_SEQ, n.CHANGED_DATE, n.CHANGED_REF_NO, n.OLD_NAME, n.OLD_LAST_NAME"
                        + ", n.NEW_NAME, n.NEW_LAST_NAME, n.EMP_SEQ, n.OLD_PREFIX_SEQ, n.NEW_PREFIX_SEQ"
                        + ", nnp.PREFIX_NAME AS NEW_PREFIX_NAME, onp.PREFIX_NAME AS OLD_PREFIX_NAME"
                        + ", CONCAT(nnp.PREFIX_NAME, n.NEW_NAME, '  ', n.NEW_LAST_NAME) AS NEW_FULL_NAME"
                        + ", CONCAT(onp.PREFIX_NAME, n.OLD_NAME, '  ', n.OLD_LAST_NAME) AS OLD_FULL_NAME"
                        + ", n.REMARKS, n.RECORD_STATUS, n.CREATED_BY, n.UPDATED_BY"
                    + " FROM HRM_PS_EMP_NAME_HISTORIES n"
                        + " LEFT JOIN MAS_NAME_PREFIXES nnp ON n.NEW_PREFIX_SEQ = nnp.NAME_PREFIX_SEQ"
                        + " LEFT JOIN MAS_NAME_PREFIXES onp ON n.OLD_PREFIX_SEQ = onp.NAME_PREFIX_SEQ"
                    + " WHERE n.RECORD_STATUS = @ActiveRecord"
                    + (hisInfo.Sequence > 0 ? " AND n.NAME_HIS_SEQ = @Sequence" : "")
                    + (empSeq > 0 ? " AND n.EMP_SEQ = @empSequence" : "");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
               
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return ds;
        }
        public void Insert(HisNameInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(13);

                dbManager.AddParameters(0, "@EmpSeq", Info.Employee.Sequence);
                dbManager.AddParameters(1, "@changed_date", Info.ChangeDate);
                dbManager.AddParameters(2, "@changed_ref_no", Info.ChangeRefNo);
                dbManager.AddParameters(3, "@old_prefix_seq", Info.OldPrefixes == null ? (int?)null : Info.OldPrefixes.Sequence);
                dbManager.AddParameters(4, "@old_name", Info.OldName);
                dbManager.AddParameters(5, "@old_last_name", Info.OldLastName);
                dbManager.AddParameters(6, "@new_prefix_seq", Info.NewPrefixes == null ? (int?)null : Info.NewPrefixes.Sequence);
                dbManager.AddParameters(7, "@new_name", Info.NewName);
                dbManager.AddParameters(8, "@new_last_name", Info.NewLastName);
                dbManager.AddParameters(9, "@Remarks", Info.Remarks);
                dbManager.AddParameters(10, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(11, "@CreatedBy", Info.CreatedBy);
                dbManager.AddParameters(12, "@CreatedWhen", DateTime.Today);

                strSQL = "INSERT INTO HRM_PS_EMP_NAME_HISTORIES"
                    + "(EMP_SEQ, CHANGED_DATE, CHANGED_REF_NO"
                        + ", OLD_PREFIX_SEQ, OLD_NAME, OLD_LAST_NAME"
                        + ", NEW_PREFIX_SEQ, NEW_NAME, NEW_LAST_NAME"
                        + ", REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES (@EmpSeq, @changed_date, @changed_ref_no, @old_prefix_seq"
                        + ", @old_name, @old_last_name, @new_prefix_seq, @new_name, @new_last_name"
                        + ", @Remarks, @RecordStatus, @CreatedBy, @CreatedWhen)";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(HisNameInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(13);

                dbManager.AddParameters(0, "@EmpSeq", Info.Employee.Sequence);
                dbManager.AddParameters(1, "@changed_date", Info.ChangeDate);
                dbManager.AddParameters(2, "@changed_ref_no", Info.ChangeRefNo);
                dbManager.AddParameters(3, "@old_prefix_seq", Info.OldPrefixes == null ? (int?)null : Info.OldPrefixes.Sequence);
                dbManager.AddParameters(4, "@old_name", Info.OldName);
                dbManager.AddParameters(5, "@old_last_name", Info.OldLastName);
                dbManager.AddParameters(6, "@new_prefix_seq", Info.NewPrefixes == null ? (int?)null : Info.NewPrefixes.Sequence);
                dbManager.AddParameters(7, "@new_name", Info.NewName);
                dbManager.AddParameters(8, "@new_last_name", Info.NewLastName);
                dbManager.AddParameters(9, "@Remarks", Info.Remarks);
                dbManager.AddParameters(10, "@Sequence", Info.Sequence);
                dbManager.AddParameters(11, "@UpdatedBy", Info.UpdatedBy);
                dbManager.AddParameters(12, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_PS_EMP_NAME_HISTORIES"
                    + " SET EMP_SEQ = @EmpSeq"
                    + ", CHANGED_DATE = @changed_date"
                    + ", CHANGED_REF_NO = @changed_ref_no"
                    + ", OLD_PREFIX_SEQ = @old_prefix_seq"
                    + ", OLD_NAME = @old_name"
                    + ", OLD_LAST_NAME = @old_last_name"
                    + ", NEW_PREFIX_SEQ = @new_prefix_seq"
                    + ", NEW_NAME = @new_name"
                    + ", NEW_LAST_NAME = @new_last_name"
                    + ", REMARKS = @Remarks"
                    + ", UPDATED_BY = @UpdatedBy"
                    + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE NAME_HIS_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(int Sequence, int UpdatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);                
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Today);
                
                strSQL = "UPDATE HRM_PS_EMP_NAME_HISTORIES"
                    + " SET RECORD_STATUS = @RecordStatus"
                    + ", UPDATED_BY = @UpdatedBy"
                    + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE NAME_HIS_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
